/*
 * $Id: TableCommand.java,v 1.10 2005/11/09 17:25:06 rbair Exp $
 *
 * Copyright 2005 Sun Microsystems, Inc., 4150 Network Circle,
 * Santa Clara, California 95054, U.S.A. All rights reserved.
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

package org.jdesktop.databuffer.provider.sql;

import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.jdesktop.databuffer.DataColumn;
import org.jdesktop.databuffer.DataRow;

/**
 * <p>A simplified SQLCommand for use with an SQLDataProvider, which acts against
 * all of the columns in a given RDBMS table. No joins are used.
 * Because this is a simple table-based DataCommand, it can infer the INSERT,
 * UPDATE, and DELETE SQL statements to use when working with the table.
 * </p>
 * <p>If you desire custom SQL, you may supply it to any of the Select SQL methods.
 * If you later want to revert to the autogenerated SQL statements, then simply
 * pass null to the setXXX methods. For example, assuming can_delete is a column
 * on the customer table:
 * <code><pre>
 *     TableCommand cmd = new TableCommand(&quot;customer&quot;);
 *     //sets a custom delete SQL statement
 *     cmd.setDeleteSQL(&quot;delete from customer where id=:id and can_delete=true&quot;);
 *     //... later, reset the delete SQL statement to the autogenerated one
 *     cmd.setDeleteSQL(null);
 * </pre></code>
 * @author rbair
 */
public class TableCommand extends SqlCommand {
    /** The Logger */
    private static final Logger LOG = Logger.getLogger(TableCommand.class.getName());
    
    /**
     * The name of the table from which to get results. If this value is
     * null, then the TableCommand is in an uninitialized state
     */
    private String tableName;
    /**
     * The where clause for this query. This is never null, but may be empty
     */
    private String whereClause = "";
    /**
     * The order by clause for this query. This is never null, but may be empty
     */
    private String orderByClause = "";
    /**
     * The having clause for this query. This is never null, but may be empty
     */
    private String havingClause = "";
    
    /** 
     * Creates a new instance of TableCommand 
     */
    public TableCommand() {
        this(null, null);
    }
    
    public TableCommand(String tableName) {
        this(tableName, null);
    }
    
    public TableCommand(String tableName, String whereClause) {
        setTableName(tableName);
        setWhereClause(whereClause);
    }
    
    /**
     * Sets the name of the table in the Database from which to load/save
     * data
     */
    public void setTableName(String tableName) {
        if (this.tableName != tableName) {
            String oldValue = this.tableName;
            this.tableName = tableName;
            firePropertyChange("tableName", oldValue, tableName);
        }
    }
    
    /**
     * Sets the where clause to use in the query. This clause *must* include
     * the &quot;where&quot; keyword
     */
    public void setWhereClause(String clause) {
        if (whereClause != clause) {
            String oldValue = this.whereClause;
            whereClause = clause == null ? "" : clause;
            firePropertyChange("whereClause", oldValue, whereClause);
        }
    }
    
    public void setOrderByClause(String clause) {
        if (orderByClause != clause) {
            String oldValue = this.orderByClause;
            orderByClause = clause == null ? "" : clause;
            firePropertyChange("orderByClause", oldValue, orderByClause);
        }
    }
    
    public void setHavingClause(String clause) {
        if (havingClause != clause) {
            String oldValue = this.havingClause;
            havingClause = clause == null ? "" : clause;
            firePropertyChange("havingClause", oldValue, havingClause);
        }
    }
    
//
//    public void executeSaveQuery(DataSet ds) {
//    	if (ds == null) {
//    		return;
//    	}
//    	
//        if (!(ds instanceof RowSetDataSet)) {
//            throw new IllegalArgumentException("The SimpleJDBCQueryTask " +
//                    "cannot save data sources that it did not generate");
//        }
//        
//        try {
//            CachedRowSet crs = ((RowSetDataSet)ds).getRowSet();
//            /*
//             * HACK! This next line (setTransactionIsolation) is a total hack,
//             * needed because the RI for CachedRowSetWriter tries to set the
//             * transaction isolation level to the CachedRowSets level.
//             * Unfortunately, the default level is unacceptable for HSQL.
//             * The RI probably needs to be hacked so that the CachedRowSetImpl
//             * will have its transaction isolation set to a level acceptable
//             * by the Database.
//             */
//            crs.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
//            crs.acceptChanges(dsc.getConnection());
//            dsc.getConnection().commit();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
//

    public String[] getParameterNames() {
	List<String> strings = new ArrayList<String>();
	if (getSelectSQL() !=  null) {
	    strings.add(getSelectSQL());
	} else {
	    strings.add(whereClause);
	    strings.add(orderByClause);
	    strings.add(havingClause);
	}
	if (getUpdateSQL() != null) {
	    strings.add(getUpdateSQL());
	}
	if (getDeleteSQL() != null) {
	    strings.add(getDeleteSQL());
	}
	if (getInsertSQL() != null) {
	    strings.add(getInsertSQL());
	}
	return super.getParameterNames(strings.toArray(new String[strings.size()]));
    }

    protected PreparedStatement getSelectStatement(JDBCDataConnection conn) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getSelectSQL() != null) {
	    return super.getSelectStatement(conn);
	}
	
	String sql = "";
        try {
            //construct the select sql by combining the tableName portion and
            //the various clause portions
            StringBuilder buffer = new StringBuilder();
            buffer.append("select * from ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" ");
            buffer.append(whereClause);
            buffer.append(" ");
            buffer.append(orderByClause);
            buffer.append(" ");
            buffer.append(havingClause);
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Select SQL: " + sql);
            return createPreparedStatement(sql, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating select SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getUpdateStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getUpdateSQL() != null) {
	    return super.getUpdateStatement(conn, row);
	}
	
	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            //construct the select sql by combining the tableName portion and
            //the various clause portions
            StringBuilder buffer = new StringBuilder();
            buffer.append("update ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" set ");
            //iterate over all of the columns in the row. Each cell that has been
            //modified needs to be included in this update statement
            List<DataColumn> columns = getUpdateableColumns(row.getTable().getColumns());
            int modCount = 0;
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (row.isModified(col)) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :" + col.getName() + ", ");
                    values.put(col.getName(), row.getValue(col));
                    modCount++;
                }
            }
            //if nothing was modified, skip this row
            if (modCount == 0) {
                return null;
            }
            //remove the trailing comma
            buffer.delete(buffer.length()-2, buffer.length());
            
            //do the where clause
            buffer.append(" where ");
            int keyColCount = 0;
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (col.isKeyColumn()) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :orig_" + col.getName() + " and ");
                    values.put("orig_" + col.getName(), row.getReferenceValue(col));
                    keyColCount++;
                }
            }
            if (keyColCount == 0) {
                LOG.log(Level.WARNING, "!!! No key columns were specified, the entire table '{0}' will be updated!!", tableName);
                //remove the where clause
                buffer.delete(buffer.length() - 7, buffer.length());
            } else {
                buffer.delete(buffer.length() - 4, buffer.length());
            }
            
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Update SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating update SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getInsertStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getInsertSQL() != null) {
	    return super.getInsertStatement(conn, row);
	}

	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            StringBuilder buffer = new StringBuilder();
            buffer.append("insert into ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append("(");
            List<DataColumn> cols = getUpdateableColumns(row.getTable().getColumns());
            for (DataColumn col : cols) {
                buffer.append("\"").append(col.getName()).append("\"");
                buffer.append(", ");
            }
            buffer.replace(buffer.length()-2, buffer.length(), ")");
            buffer.append(" values(");
            for (DataColumn col : cols) {
                buffer.append(":" + col.getName() + ", ");
                values.put(col.getName(), row.getValue(col));
            }
            buffer.replace(buffer.length()-2, buffer.length(), ")");
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Insert SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating insert SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }

    protected PreparedStatement getDeleteStatement(JDBCDataConnection conn, DataRow row) throws Exception {
        if (tableName == null) {
            //this TableCommand has not been configured, throw an exception
            throw new Exception("TableCommand not configured with a table name");
        }

	//use the custom SQL, if there is any
	if (super.getDeleteSQL() != null) {
	    return super.getDeleteStatement(conn, row);
	}
	
	String sql = "";
        try {
            Map<String,Object> values = new HashMap<String,Object>();
            StringBuilder buffer = new StringBuilder();
            buffer.append("delete from ");
            buffer.append("\"").append(tableName).append("\"");
            buffer.append(" where ");
            int keyColCount = 0;
            List<DataColumn> columns = row.getTable().getColumns();
            for (int i=0; i<columns.size(); i++) {
                DataColumn col = columns.get(i);
                if (col.isKeyColumn()) {
                    buffer.append("\"").append(col.getName()).append("\"");
                    buffer.append(" = :orig_" + col.getName() + " and ");
                    values.put("orig_" + col.getName(), row.getReferenceValue(col));
                    keyColCount++;
                }
            }
            if (keyColCount == 0) {
                LOG.log(Level.WARNING, "!!! No key columns were specified, the entire table '" + tableName + "' will be deleted!!");
                //remove the where clause
                buffer.delete(buffer.length() - 7, buffer.length());
            } else {
                buffer.delete(buffer.length() - 4, buffer.length());
            }
            
            sql = buffer.toString().trim();
            LOG.log(Level.FINE, "Generated Delete SQL: " + sql);
            return super.prepareStatement(sql, values, conn);
        } catch (Exception e) {
	    LOG.log(Level.WARNING, "Problem with creating delete SQL statement {0}", sql);
	    LOG.log(Level.WARNING, e.getMessage(), e);
            return null;
        }
    }
    
    /**
     * Returns a list of columns that can be involved in an update or insert
     * statement
     */
    private List<DataColumn> getUpdateableColumns(List<DataColumn> cols) {
        List<DataColumn> results = new ArrayList<DataColumn>();
        for (DataColumn col : cols) {
            if (col.getExpression() == null || col.getExpression().equals("")) {
                results.add(col);
            }
        }
        return results;
    }
}